﻿/******************************************************************************
'Description:订单完成情况看板
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:zhunan
'create Date:2021.03.25
 ********************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace ReportBusiness
{
    public class MfgorderStatusInfoBusiness
    {
        public uMESReportOracleDAL dal = new uMESReportOracleDAL();
        private uMESCommonMethod common = new uMESCommonMethod();

        public DataTable GetFactoryInfo(string factoryid)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT f.factoryid,f.factoryname FROM factory f WHERE f.factoryid ='{0}'", factoryid);
            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        public DataTable GetWorkcentername(string factoryid)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT w.workcentername,w.workcenterid");
            sb.AppendLine("FROM workcenter w");
            sb.AppendFormat("WHERE w.wctype IS NULL AND w.factoryid ='{0}' AND w.notes IS NULL ", factoryid);
            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        // 模糊匹配查询件号 add zhunan 2018.10.23
        public DataTable dtGetProductname(Dictionary<string, object> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT pb.productname");
            sb.AppendLine("FROM productbase pb");
            sb.AppendFormat("WHERE pb.productname LIKE '%{0}%'", para["productname"].ToString().ToUpper());
            sb.AppendLine(" AND ROWNUM < 11");

            DataTable dt = dal.GetDataTable(sb.ToString());
            return dt;
        }

        //查询批次总条数 add zhunan 2021.4.14
        public int GetTotal(Dictionary<string, object> para)
        {
            string sql = @"
SELECT * FROM 
(
SELECT DISTINCT mfg.mfgordername,mfg.plannedstartdate,mfg.plannedcompletiondate,NULL originalstartdate,NULL isselfmfg,NULL materialdate,
       CASE WHEN fp.productname IS NOT NULL THEN '重点件号' ELSE '非重点件号' END isfocusProcuct,
       NULL containername,NULL containerid,NULL qty,NULL workcentername,NULL isinstore,NULL workflowstepname,NULL workflowstepid,NULL workflowid,0 maxSequence
FROM mfgorder mfg
LEFT JOIN containercurrentinfo cci ON mfg.mfgorderid = cci.mfgorderid
LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
LEFT JOIN focusproduct fp ON pb.productname = fp.productname
WHERE 1=1 
";
            //订单所属分厂
            if (para.ContainsKey("factoryid") && para["factoryid"].ToString() != "")
            {
                sql += string.Format(" AND mfg.factoryid = '{0}'", para["factoryid"].ToString());
            }
            //件号
            if (para.ContainsKey("productname") && para["productname"].ToString() != "")
            {
                sql += string.Format(" AND pb.productname like '%{0}%'", para["productname"].ToString());
            }
            //订单号
            if (para.ContainsKey("mfgordername") && para["mfgordername"].ToString() != "")
            {
                sql += string.Format(" AND mfg.mfgordername LIKE '%{0}%'", para["mfgordername"].ToString());
            }

            //是否重点件号
            if (para.ContainsKey("isFocusProductname"))
            {
                if (para["isFocusProductname"].ToString() == "重点件号")
                {
                    sql += string.Format(" AND fp.productname IS NOT NULL ");
                }
                if (para["isFocusProductname"].ToString() == "非重点件")
                {
                    sql += string.Format(" AND fp.productname IS NULL ");
                }
            }

            //订单状态
            if (para.ContainsKey("status"))
            {
                if (para["status"].ToString() == "未开卡" || para["status"].ToString() == "全部")
                {
                    sql += " AND cci.containerid IS NULL ";
                }
                else
                {
                    sql += " AND mfg.mfgordername IS NULL ";
                }
            }

            //订单计划完成日期
            if (para.ContainsKey("startDate") && para["startDate"].ToString() != "")
            {
                sql += string.Format(" AND trunc(mfg.plannedcompletiondate) >= to_date('{0}','yyyy-mm-dd') ", para["startDate"].ToString());
            }
            if (para.ContainsKey("endDate") && para["endDate"].ToString() != "")
            {
                sql += string.Format(" AND trunc(mfg.plannedcompletiondate) <= to_date('{0}','yyyy-mm-dd') ", para["endDate"].ToString());
            }

            //流水卡号
            if (para.ContainsKey("containername") && para["containername"].ToString() != "")
            {
                sql += " AND cci.containername is not null ";
            }

            sql += " UNION ALL ";
            sql += @"
SELECT DISTINCT mfg.mfgordername,mfg.plannedstartdate,mfg.plannedcompletiondate,c.originalstartdate,
       DECODE(cci.isselfmfg,0,'需要领料',1,'不需领料') isselfmfg,NVL(cci.materialdate,mt.materialdate) materialdate,
       CASE WHEN fp.productname IS NOT NULL THEN '重点件号' ELSE '非重点件号' END isfocusProcuct,
       cci.containername,cci.containerid,cci.qty,w.workcentername,cci.isinstore,cci.workflowstepname,cci.workflowstepid,cci.workflowid,
       NVL(wf.maxSequence,0)  maxSequence
FROM containercurrentinfo cci
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = cci.mfgorderid
LEFT JOIN focusproduct fp ON fp.productname = cci.productname
LEFT JOIN Workcenter w ON w.workcenterid = cci.workcenterid
LEFT JOIN (
            SELECT hm.txndate materialdate,cci.containerid,cci.containername
            FROM historymainline hm
            LEFT JOIN Workflowstep ws ON ws.workflowstepid = hm.workflowstepid
            LEFT JOIN cdodefinition cdo ON cdo.cdodefid = hm.txntype
            INNER JOIN containercurrentinfo cci ON cci.containerid = hm.containerid
            WHERE ws.workflowstepname = '到料确认' AND (cdo.cdoname = 'MoveStd' OR cdo.cdoname ='MoveNonStd') AND cci.isselfmfg = 0 AND cci.materialdate IS NULL 
                  AND hm.txndate IS NOT NULL
          ) mt ON mt.containerid = cci.containerid
LEFT JOIN ( SELECT ws.workflowid,COUNT(ws.workflowstepid) maxSequence FROM Workflowstep ws GROUP BY ws.workflowid ) wf ON wf.workflowid = cci.workflowid
WHERE cci.workflowroute IS NULL AND cci.containerstatus <> 2
";
            //订单所属分厂
            if (para.ContainsKey("factoryid") && para["factoryid"].ToString() != "")
            {
                sql += string.Format(" AND mfg.factoryid = '{0}'", para["factoryid"].ToString());
            }
            //工区
            if (para.ContainsKey("workcenterid") && para["workcenterid"].ToString() != "全部")
            {
                sql += string.Format(" AND cci.fromworkcenterid = '{0}'", para["workcenterid"].ToString());
            }
            //订单号
            if (para.ContainsKey("mfgordername") && para["mfgordername"].ToString() != "")
            {
                sql += string.Format(" AND mfg.mfgordername LIKE '%{0}%'", para["mfgordername"].ToString());
            }
            //件号
            if (para.ContainsKey("productname") && para["productname"].ToString() != "")
            {
                sql += string.Format(" AND cci.productname like '%{0}%'", para["productname"].ToString());
            }
            //是否重点件号
            if (para.ContainsKey("isFocusProductname"))
            {
                if (para["isFocusProductname"].ToString() == "重点件号")
                {
                    sql += string.Format(" AND fp.productname IS NOT NULL ");
                }
                if (para["isFocusProductname"].ToString() == "非重点件")
                {
                    sql += string.Format(" AND fp.productname IS NULL ");
                }
            }
            //流水卡号
            if (para.ContainsKey("containername") && para["containername"].ToString() != "")
            {
                sql += string.Format(" AND cci.containername like '%{0}%' ", para["containername"].ToString());
            }

            //订单状态
            if (para.ContainsKey("status"))
            {
                if (para["status"].ToString() == "已开卡未到料")
                {
                    sql += string.Format(" AND cci.isinstore = 0 AND cci.isselfmfg = 0 AND NVL(cci.materialdate,mt.materialdate) IS NULL ");
                }
                if (para["status"].ToString() == "已到料未完成")
                {
                    sql += string.Format(" AND cci.isinstore = 0 AND ((cci.isselfmfg = 0 AND NVL(cci.materialdate,mt.materialdate) IS NOT NULL) OR (cci.isselfmfg = 1)) ");
                }
                if (para["status"].ToString() == "已完成")
                {
                    sql += string.Format(" AND cci.isinstore = 1 ");
                }
                if (para["status"].ToString() == "未开卡")
                {
                    sql += " AND cci.containername is null ";
                }
                if (para["status"].ToString() == "全部")
                {
                    sql += " AND ((cci.isinstore = 0 AND cci.isselfmfg = 0 AND NVL(cci.materialdate,mt.materialdate) IS NULL) OR (cci.isinstore = 0 AND ((cci.isselfmfg = 0 AND NVL(cci.materialdate,mt.materialdate) IS NOT NULL) OR (cci.isselfmfg = 1))) OR (cci.isinstore = 1)) ";
                }
            }
            //订单计划完成日期
            if (para.ContainsKey("startDate") && para["startDate"].ToString() != "")
            {
                sql += string.Format(" AND trunc(mfg.plannedcompletiondate) >= to_date('{0}','yyyy-mm-dd') ", para["startDate"].ToString());
            }
            if (para.ContainsKey("endDate") && para["endDate"].ToString() != "")
            {
                sql += string.Format(" AND trunc(mfg.plannedcompletiondate) <= to_date('{0}','yyyy-mm-dd') ", para["endDate"].ToString());
            }

            sql += " ) t ORDER BY t.plannedcompletiondate,t.containername";
            return dal.GetDataTable(sql).Rows.Count;
        }

        //查询订单信息（单件单卡不合并） add zhunan 2021.4.14
        public DataTable GetMfgContainerInfoData(Dictionary<string, object> para, out int pageCount, out int containerCount, out int rowCount, out int maxSequence)
        {
            string sql = @"
SELECT * FROM 
(
SELECT DISTINCT mfg.mfgordername,mfg.plannedstartdate,mfg.plannedcompletiondate,NULL originalstartdate,NULL isselfmfg,NULL materialdate,
       CASE WHEN fp.productname IS NOT NULL THEN '重点件号' ELSE '非重点件号' END producttype,cci.productdescription,
       NULL containername,NULL containerid,NULL qty,NULL workcentername,NULL isinstore,NULL workflowstepname,NULL workflowstepid,1 workflowstepsequence,NULL workflowid,1 maxSequence
FROM mfgorder mfg
LEFT JOIN containercurrentinfo cci ON mfg.mfgorderid = cci.mfgorderid
LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
LEFT JOIN focusproduct fp ON pb.productname = fp.productname
WHERE 1=1 
";
            #region 查询条件
            //订单所属分厂
            if (para.ContainsKey("factoryid") && para["factoryid"].ToString() != "")
            {
                sql += string.Format(" AND mfg.factoryid = '{0}'", para["factoryid"].ToString());
            }

            //订单号
            if (para.ContainsKey("mfgordername") && para["mfgordername"].ToString() != "")
            {
                sql += string.Format(" AND mfg.mfgordername LIKE '%{0}%'", para["mfgordername"].ToString());
            }

            //是否重点件号
            if (para.ContainsKey("isFocusProductname"))
            {
                if (para["isFocusProductname"].ToString() == "重点件号")
                {
                    sql += string.Format(" AND fp.productname IS NOT NULL ");
                }
                if (para["isFocusProductname"].ToString() == "非重点件")
                {
                    sql += string.Format(" AND fp.productname IS NULL ");
                }
            }

            //订单状态
            if (para.ContainsKey("status"))
            {
                if (para["status"].ToString() == "未开卡" || para["status"].ToString() == "全部")
                {
                    sql += " AND cci.containerid IS NULL ";
                }
                else
                {
                    sql += " AND mfg.mfgordername IS NULL ";
                }
            }

            //订单计划完成日期
            if (para.ContainsKey("startDate") && para["startDate"].ToString() != "")
            {
                sql += string.Format(" AND trunc(mfg.plannedcompletiondate) >= to_date('{0}','yyyy-mm-dd') ", para["startDate"].ToString());
            }
            if (para.ContainsKey("endDate") && para["endDate"].ToString() != "")
            {
                sql += string.Format(" AND trunc(mfg.plannedcompletiondate) <= to_date('{0}','yyyy-mm-dd') ", para["endDate"].ToString());
            }

            //流水卡号
            if (para.ContainsKey("containername") && para["containername"].ToString() != "")
            {
                sql += " AND cci.containername is not null ";
            }

            #endregion

            sql += " UNION ALL ";
            sql += @"
SELECT DISTINCT mfg.mfgordername,mfg.plannedstartdate,mfg.plannedcompletiondate,c.originalstartdate,
       DECODE(cci.isselfmfg,0,'需要领料',1,'不需领料') isselfmfg,NVL(cci.materialdate,mt.materialdate) materialdate,
       CASE WHEN fp.productname IS NOT NULL THEN '重点件号' ELSE '非重点件号' END producttype,cci.productdescription,
       cci.containername,cci.containerid,cci.qty,w.workcentername,cci.isinstore,cci.workflowstepname,cci.workflowstepid,cci.workflowstepsequence,cci.workflowid,
       NVL(wf.maxSequence,0)  maxSequence
FROM containercurrentinfo cci
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = cci.mfgorderid
LEFT JOIN focusproduct fp ON fp.productname = cci.productname
LEFT JOIN Workcenter w ON w.workcenterid = cci.workcenterid
LEFT JOIN ( SELECT ws.workflowid,COUNT(ws.workflowstepid) maxSequence FROM Workflowstep ws GROUP BY ws.workflowid ) wf ON wf.workflowid = cci.workflowid
LEFT JOIN (
            SELECT hm.txndate materialdate,cci.containerid,cci.containername
            FROM historymainline hm
            LEFT JOIN Workflowstep ws ON ws.workflowstepid = hm.workflowstepid
            LEFT JOIN cdodefinition cdo ON cdo.cdodefid = hm.txntype
            INNER JOIN containercurrentinfo cci ON cci.containerid = hm.containerid
            WHERE ws.workflowstepname = '到料确认' AND (cdo.cdoname = 'MoveStd' OR cdo.cdoname ='MoveNonStd') AND cci.isselfmfg = 0 AND cci.materialdate IS NULL 
                  AND hm.txndate IS NOT NULL
          ) mt ON mt.containerid = cci.containerid
WHERE cci.workflowroute IS NULL AND cci.containerstatus <> 2
";
            #region 查询条件
            //订单所属分厂
            if (para.ContainsKey("factoryid") && para["factoryid"].ToString() != "")
            {
                sql += string.Format(" AND mfg.factoryid = '{0}'", para["factoryid"].ToString());
            }
            //工区
            if (para.ContainsKey("workcenterid") && para["workcenterid"].ToString() != "全部")
            {
                sql += string.Format(" AND cci.fromworkcenterid = '{0}'", para["workcenterid"].ToString());
            }
            //订单号
            if (para.ContainsKey("mfgordername") && para["mfgordername"].ToString() != "")
            {
                sql += string.Format(" AND mfg.mfgordername LIKE '%{0}%'", para["mfgordername"].ToString());
            }
            //件号
            if (para.ContainsKey("productname") && para["productname"].ToString() != "")
            {
                sql += string.Format(" AND cci.productname like '%{0}%'", para["productname"].ToString());
            }
            //是否重点件号
            if (para.ContainsKey("isFocusProductname"))
            {
                if (para["isFocusProductname"].ToString() == "重点件号")
                {
                    sql += string.Format(" AND fp.productname IS NOT NULL ");
                }
                if (para["isFocusProductname"].ToString() == "非重点件")
                {
                    sql += string.Format(" AND fp.productname IS NULL ");
                }
            }
            //流水卡号
            if (para.ContainsKey("containername") && para["containername"].ToString() != "")
            {
                sql += string.Format(" AND cci.containername like '%{0}%' ", para["containername"].ToString());
            }

            //订单状态
            if (para.ContainsKey("status"))
            {
                if (para["status"].ToString() == "已开卡未到料")
                {
                    sql += string.Format(" AND cci.isinstore = 0 AND cci.isselfmfg = 0 AND NVL(cci.materialdate,mt.materialdate) IS NULL ");
                }
                if (para["status"].ToString() == "已到料未完成")
                {
                    sql += string.Format(" AND cci.isinstore = 0 AND ((cci.isselfmfg = 0 AND NVL(cci.materialdate,mt.materialdate) IS NOT NULL) OR (cci.isselfmfg = 1)) ");
                }
                if (para["status"].ToString() == "已完成")
                {
                    sql += string.Format(" AND cci.isinstore = 1 ");
                }
                if (para["status"].ToString() == "未开卡")
                {
                    sql += " AND cci.containername is null ";
                }
                if (para["status"].ToString() == "全部")
                {
                    sql += " AND ((cci.isinstore = 0 AND cci.isselfmfg = 0 AND NVL(cci.materialdate,mt.materialdate) IS NULL) OR (cci.isinstore = 0 AND ((cci.isselfmfg = 0 AND NVL(cci.materialdate,mt.materialdate) IS NOT NULL) OR (cci.isselfmfg = 1))) OR (cci.isinstore = 1)) ";
                }
            }
            //订单计划完成日期
            if (para.ContainsKey("startDate") && para["startDate"].ToString() != "")
            {
                sql += string.Format(" AND trunc(mfg.plannedcompletiondate) >= to_date('{0}','yyyy-mm-dd') ", para["startDate"].ToString());
            }
            if (para.ContainsKey("endDate") && para["endDate"].ToString() != "")
            {
                sql += string.Format(" AND trunc(mfg.plannedcompletiondate) <= to_date('{0}','yyyy-mm-dd') ", para["endDate"].ToString());
            }
            #endregion

            sql += " ) t ORDER BY t.plannedcompletiondate,t.containername";

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, Convert.ToInt32(para["currentPage"]), Convert.ToInt32(para["pageSize"]));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);
            containerCount = 0;
            maxSequence = 0;

            DataTable dtMain = retR.DBTable;

            if (dtMain.Rows.Count == 0)
            {
                return new DataTable();
            }

            containerCount = dtMain.Rows.Count;

            maxSequence = Convert.ToInt32(dtMain.Compute("Max(maxSequence)", "mfgordername is not null"));

            DataTable dtResult = new DataTable();
            dtResult.Columns.Add("mfgordername");//订单号
            dtResult.Columns.Add("plannedstartdate");//订单计划开始日期
            dtResult.Columns.Add("plannedcompletiondate");//订单计划完成日期
            dtResult.Columns.Add("materialdate");//到料日期
            dtResult.Columns.Add("originalstartdate");//开卡日期
            dtResult.Columns.Add("fromworkcentername");//责任工区
            dtResult.Columns.Add("producttype");//是否重点件号
            dtResult.Columns.Add("productdescription");//零件名称
            dtResult.Columns.Add("containername");//流水卡号
            dtResult.Columns.Add("qty");//批次数量
            dtResult.Columns.Add("currentworkflowstepname");//当前工序
            dtResult.Columns.Add("workflowstepsequence");//当前工序的工序号
            dtResult.Columns.Add("workflowstepname");//工序名称
            dtResult.Columns.Add("sequence");//工序号
            dtResult.Columns.Add("plannedfinishdate");//排产计划日期
            dtResult.Columns.Add("fullname");//派工给工人
            dtResult.Columns.Add("checkdate");//完工日期
            dtResult.Columns.Add("status");//状态
            dtResult.Columns.Add("isselfmfg");//是否需要领料

            foreach (DataRow dr in dtMain.Rows)
            {
                if (dr["containerid"].ToString() != "")
                {
                    string filter = string.Format("containerid = '{0}'", dr["containerid"]);
                    DataRow[] drList = GetWorkflowstepPlanInfo(dr["containerid"].ToString()).Select(filter);
                    for (int i = 0; i < drList.Length; i++)
                    {
                        DataRow datarow = dtResult.NewRow();
                        datarow["mfgordername"] = dr["mfgordername"];//订单号
                        datarow["plannedstartdate"] = dr["plannedstartdate"];//订单计划开始日期
                        datarow["plannedcompletiondate"] = dr["plannedcompletiondate"];//订单计划完成日期
                        datarow["materialdate"] = dr["materialdate"];//到料日期
                        datarow["originalstartdate"] = dr["originalstartdate"];//开卡日期
                        datarow["fromworkcentername"] = dr["workcentername"];//责任工区
                        datarow["producttype"] = dr["producttype"];//是否重点件号
                        datarow["productdescription"] = dr["productdescription"];//零件名称
                        datarow["containername"] = dr["containername"];//流水卡号
                        datarow["qty"] = dr["qty"];//批次数量
                        datarow["currentworkflowstepname"] = dr["workflowstepname"];//当前工序
                        datarow["workflowstepsequence"] = dr["workflowstepsequence"];//当前工序的工序号
                        datarow["isselfmfg"] = dr["isselfmfg"];//是否需要领料

                        datarow["workflowstepname"] = drList[i]["workflowstepname"];//工序名称
                        datarow["sequence"] = drList[i]["sequence"];//工序号
                        datarow["plannedfinishdate"] = drList[i]["plannedfinishdate"];//排产计划日期
                        datarow["fullname"] = drList[i]["fullname"];//派工给工人
                        datarow["checkdate"] = drList[i]["checkdate"];//完工日期
                        datarow["status"] = drList[i]["status"];//状态
                        dtResult.Rows.Add(datarow);
                    }

                    if (drList.Length < maxSequence)
                    {
                        for (int i = 0; i < (maxSequence - drList.Length); i++)
                        {
                            DataRow datarow = dtResult.NewRow();
                            datarow["fromworkcentername"] = dr["workcentername"];
                            datarow["containername"] = dr["containername"];
                            dtResult.Rows.Add(datarow);
                        }
                    }
                }
                else
                {
                    for (int i = 0; i < maxSequence; i++)
                    {
                        DataRow datarow = dtResult.NewRow();
                        datarow["mfgordername"] = dr["mfgordername"];//订单号
                        datarow["plannedstartdate"] = dr["plannedstartdate"];//订单计划开始日期
                        datarow["plannedcompletiondate"] = dr["plannedcompletiondate"];//订单计划完成日期
                        datarow["materialdate"] = dr["materialdate"];//到料日期
                        datarow["originalstartdate"] = dr["originalstartdate"];//开卡日期
                        datarow["fromworkcentername"] = dr["workcentername"];//责任工区
                        datarow["producttype"] = dr["producttype"];//是否重点件号
                        datarow["productdescription"] = dr["productdescription"];//零件名称
                        datarow["containername"] = dr["containername"];//流水卡号
                        datarow["qty"] = dr["qty"];//批次数量
                        datarow["currentworkflowstepname"] = dr["workflowstepname"];//当前工序
                        datarow["workflowstepsequence"] = dr["workflowstepsequence"];//当前工序的工序号
                        datarow["workflowstepname"] = "";//工序名称
                        datarow["sequence"] = "";//工序号
                        datarow["plannedfinishdate"] = "";//排产计划日期
                        datarow["fullname"] = "";//派工给工人
                        datarow["checkdate"] = "";//完工日期
                        datarow["status"] = "";//状态
                        datarow["isselfmfg"] = "";//是否需要领料
                        dtResult.Rows.Add(datarow);
                    }
                }
            }

            return dtResult;
        }

        //查询工序完工情况 add zhunan 2021.4.14
        public DataTable GetWorkflowstepPlanInfo(string containerid) {
            string sql = @"
SELECT cci.containerid,cci.containername,ws.sequence,ws.workflowstepname,
       CASE WHEN cci.workflowstepname = ws.workflowstepname THEN '当前序' ELSE '非当前序' END iscurrentspec,
       t.fullname,
       NVL(spr.needcompletedate,csp.plannedfinishdate) plannedfinishdate,NVL(ship.recivedate,csr.checkdate) checkdate,
       CASE WHEN NVL(ship.recivedate,csr.checkdate) IS NOT NULL AND NVL(spr.needcompletedate,csp.plannedfinishdate) IS NOT NULL AND TRUNC(NVL(ship.recivedate,csr.checkdate)) - TRUNC(NVL(spr.needcompletedate,csp.plannedfinishdate)) > 0 THEN '超期完成'
            WHEN NVL(ship.recivedate,csr.checkdate) IS NOT NULL AND NVL(spr.needcompletedate,csp.plannedfinishdate) IS NOT NULL AND TRUNC(NVL(ship.recivedate,csr.checkdate)) - TRUNC(NVL(spr.needcompletedate,csp.plannedfinishdate)) <= 0 THEN '按时完成'
            WHEN NVL(ship.recivedate,csr.checkdate) IS NOT NULL AND NVL(spr.needcompletedate,csp.plannedfinishdate) IS NULL THEN '按时完成'
            WHEN NVL(ship.recivedate,csr.checkdate) IS NULL AND NVL(spr.needcompletedate,csp.plannedfinishdate) IS NOT NULL AND TRUNC(SYSDATE) - TRUNC(NVL(spr.needcompletedate,csp.plannedfinishdate)) > 0 THEN '超期未完成'
            WHEN NVL(spr.needcompletedate,csp.plannedfinishdate) IS NULL THEN '工序未排产' END status
FROM containercurrentinfo cci
LEFT JOIN workflowstep ws ON ws.workflowid = cci.workflowid
LEFT JOIN containerstepplan csp ON csp.containerid = cci.containerid AND csp.workflowstepid = ws.workflowstepid
LEFT JOIN containersteprecord csr ON csr.containerid = cci.containerid AND csr.workflowstepid = ws.workflowstepid
LEFT JOIN containershiprecive ship ON ship.containerid = cci.containerid AND ship.workflowstepid = ws.workflowstepid
LEFT JOIN containershiprecord spr ON spr.containerid = cci.containerid AND spr.workflowstepid = ws.workflowstepid
LEFT JOIN (
    SELECT n.workflowstepname,listagg(n.fullname,'、') WITHIN GROUP (ORDER BY n.fullname) fullname
    FROM (
        SELECT DISTINCT ws.workflowstepname,NVL(e.fullname,e2.fullname) fullname
        FROM containerstepdispatch csd
        LEFT JOIN containerstepdispatchdetail csdd ON csdd.containerstepdispatchid = csd.containerstepdispatchid
        LEFT JOIN employee e ON e.employeeid = csd.workerid
        LEFT JOIN employee e2 ON e2.employeeid = csdd.workerid
        LEFT JOIN Workflowstep ws ON ws.workflowstepid = csd.workflowstepid
        WHERE csd.isuse = 0 ";
            sql += string.Format(" AND csd.containerid = '{0}'", containerid);
            sql += @"
    ) n GROUP BY n.workflowstepname 
) t ON t.workflowstepname = ws.workflowstepname
WHERE ws.sequence IS NOT NULL AND cci.workflowroute IS NULL AND cci.containerstatus <> 2 ";

            sql += string.Format(" AND cci.containerid = '{0}'",containerid);
            sql += @"
GROUP BY cci.containerid,cci.containername,ws.sequence,cci.workflowstepname,ws.workflowstepname,t.fullname,csp.plannedfinishdate,csr.checkdate,ship.recivedate,spr.needcompletedate
ORDER BY cci.containername,ws.sequence
";
            return dal.GetDataTable(sql);
        }
    }
}
